﻿using Dapper;
using Domain.Entity.ord;
using Domain.IRepository;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repository
{
    public class PurchaseReturn_dRepository : IPurchaseReturn_dRepository
    {

        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";


        //添加退货明细
        public async Task<int> AddPurchaseReturn_d(PurchaseReturn_d purchaseReturn_D)
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"insert into [dbo].[ord_purchase_return_d](purt_id, purd_id, proc_id, proc_name, purtd_num,purtd_price, purtd_outstockOK_num, purtd_outstockNO_num, purtd_total, remark)
                                values({purchaseReturn_D.purt_id},{purchaseReturn_D.purd_id},{purchaseReturn_D.proc_id},'{purchaseReturn_D.proc_name}',{purchaseReturn_D.purtd_num},'{purchaseReturn_D.purtd_price}',{purchaseReturn_D.purtd_outstockOK_num},{purchaseReturn_D.purtd_outstockNO_num},'{purchaseReturn_D.purtd_total}','{purchaseReturn_D.remark}')";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        //删除
        public async Task<int> DeletePurchaseReturn_d(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"Delete from [dbo].[ord_purchase_return_d] where purtd_id={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //根据进货退货单与货品获取到主键id
        public async Task<int> Getid(int purtid, int procid)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"select purtd_id from [dbo].[ord_purchase_return_d] where purt_id={purtid} and proc_id={procid}";
                return await sqlConnection.ExecuteScalarAsync<int>(sql);
            }
        }


        //根据purtId查询明细
        public async Task<List<PurchaseReturn_d>> GetPurchaseReturn_Ds(int purtId)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select * from [dbo].[ord_purchase_return_d] where purt_id={purtId}";
                return sqlConnection.Query<PurchaseReturn_d>(sql).ToList();
            }
        }

        //更新出库数
        public async Task<int> UpdateNum(int purtdid, int oknum, int nonum)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[ord_purchase_return_d] set purtd_outstockOK_num={oknum},purtd_outstockNO_num={nonum} where purtd_id={purtdid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        //修改
        public async Task<int> UpdatePurchaseReturn_d(PurchaseReturn_d purchaseReturn_D)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[ord_purchase_return_d] set purt_id={purchaseReturn_D.purt_id},purd_id={purchaseReturn_D.purd_id},proc_id={purchaseReturn_D.proc_id},proc_name='{purchaseReturn_D.proc_name}',
                                purtd_num={purchaseReturn_D.purtd_num},purtd_price='{ purchaseReturn_D.purtd_price}',purtd_outstockOK_num={purchaseReturn_D.purtd_outstockOK_num},purtd_outstockNO_num={purchaseReturn_D.purtd_outstockNO_num},purtd_total='{purchaseReturn_D.purtd_total}',remark='{purchaseReturn_D.remark}' where purtd_id={purchaseReturn_D.purtd_id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }
    }
}
